package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class VclLoadRemedyStatOneDAO {
	private MyConnectDSTransDAO mcdstDAO = new MyConnectDSTransDAO();
	private IBaseDAO baseDAO;
    
	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}

	
	
	/**
	 * 获取设备状态统计表标记
	 * @param statTime
	 * @return
	 */
    public Map<String, Object> getStateSign(String statTime){
    	Map<String, Object> map = new HashMap<String,Object>();
    	String sql = "select Msg_ESSC_Complete " +
    			" from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_Complete", "3", null)+" where Msg_ESSC_Date = '"+statTime+"' ";
    	map = baseDAO.execQueryOne(sql, new Object[]{});
    	return map;
    }
    
	/**
	 *更新设备状态统计表标记
	 * @param statTime
	 * @return
	 */
    public void setStateSign(String statTime){
    	Map<String, Object> map = new HashMap<String,Object>();
    	String sql = "update "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_Complete", "3", null)+" set Msg_ESSC_Load_Complete = 1 where Msg_ESSC_Date = '"+statTime+"' ";
    	baseDAO.execUpdate(sql, new Object[]{});
    }
    
	/**
	 * 获取设备状态统计表平均载重
	 * @param statTime
	 * @return
	 */
    public Map<String, Object> getVclStateLoad(String statTime,String vclid){
    	Map<String, Object> map = new HashMap<String,Object>();
    	String sql = "select MsgESS_AverageLoad " +
    			" from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_"+statTime.replaceAll("-",""), "3", null)+" where MsgESS_Vcl_ID = "+vclid+" ";
    	map = baseDAO.execQueryOne(sql, new Object[]{});
    	return map;
    }
    
	/**
	 * 获取载重统计各时间段
	 * @param statTime
	 * @return
	 */
    public List<Map<String, Object>> getLoadTimeSec(String statTime){
    	List<Map<String, Object>> listTimeSec = new ArrayList<Map<String,Object>>();
    	String sql = "select MsgTPL_ID as MsgESLS_ID,MsgTPL_Vcl_ID as MsgESLS_Vcl_ID,MsgTPL_iWork as MsgESLS_IntervalWorkTime,MsgTPL_AverageLoad as MsgESLS_AverageLoad,convert(varchar(19),MsgTPL_BeginTime,120) as beTime,convert(varchar(19),MsgTPL_EndTime,120) as endTime" +
    			" from "+GlobalDBVar.getDBFullName_Conn("Msg_Timing_Pstn_Layering_"+statTime.replaceAll("-",""), "3", null)+" inner join LoadRemedyVcl on LoadRV_VclID = MsgTPL_Vcl_ID ";
    	listTimeSec = baseDAO.execQuery(sql, new Object[]{});
    	return listTimeSec;
    }
	/**
	 * 获取载重统计各时间段(一台设备)
	 * @param statTime
	 * @return
	 */
    public List<Map<String, Object>> getLoadTimeSecByVclid(String statTime,String vclId){
    	List<Map<String, Object>> listTimeSec = new ArrayList<Map<String,Object>>();
    	String sql = "select MsgESLS_ID,MsgESLS_Vcl_ID,convert(varchar(19),MsgESLS_IntervalBeginTime,120) as beTime,convert(varchar(19),MsgESLS_IntervalEndTime,120) as endTime" +
    			" from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_LoadStatistics_"+statTime.replaceAll("-",""), "3", null)+" where MsgESLS_Vcl_ID = "+vclId+" ";
    	listTimeSec = baseDAO.execQuery(sql, new Object[]{});
    	return listTimeSec;
    }
	/**
	 * 获取时间段内三个参数
	 * @param statTime
	 * @return
	 */
    public List<Map<String, Object>> getPPParamSec(String statTime,String beTime,String endTime,String vclid){
    	List<Map<String, Object>> listParamSec = new ArrayList<Map<String,Object>>();
    	String sql = "select MsgU_Vcl_ID,MsgU_MsgTime,MsgU_UDSValue6 as MsgUC_UDSValueB, " +
    			" MsgU_UDSValue5 as MsgUC_UDSValueC," +
    			" MsgU_UDSValue4 as MsgUC_UDSValueF " +
    			" from "+GlobalDBVar.getDBFullName_Conn("Msg_UDS_"+statTime.replaceAll("-",""), "4", statTime)+"" +
    			" WHERE MsgU_MsgTime>='"+beTime+"' AND MsgU_MsgTime<='"+endTime+"' " +
    			" and MsgU_Vcl_ID = "+vclid+" ";
    	listParamSec = baseDAO.execQuery(sql, new Object[]{});
    	return listParamSec;
    }
    
    /**
	 * 获取时间段内三个参数
	 * @param statTime
	 * @return
	 */
    public List<Map<String, Object>> getPPParamSecCS(String statTime,String beTime,String endTime,String vclid){
    	List<Map<String, Object>> listParamSec = new ArrayList<Map<String,Object>>();
    	String sql = "select vehicleid,vehicleNumber,msgtime,rectime,bsx as MsgUC_UDSValueB,speed as MsgUC_UDSValueC ,fdj as MsgUC_UDSValueF " +
    			" from Sheet2$ where msgtime>=?  and msgtime<=? ";
    	listParamSec = baseDAO.execQuery(sql, new Object[]{beTime,endTime});
    	return listParamSec;
    }
    
	/**
	 * 获取设备设定档位及车速设定
	 * @param statTime
	 * @return
	 */
    public List<Map<String, Object>> getVclGearSet(String vclid){
    	List<Map<String, Object>> listVclGear = new ArrayList<Map<String,Object>>();
    	String sql = " select  top 4 ParaSDD2_Grear,ParaSDD2_DriRatio,ParaSD2_RearAxleRatio,ParaSD2_TireRadius,ParaSD2_Vcl_ID from ParaSetDriver2 inner join  ParaSetDetailedDriver2 " +
    			" on ParaSDD2_ParaSD2_ID = ParaSD2_ID where ParaSD2_Vcl_ID = "+vclid+" " +
    			" and ParaSDD2_SetStatus = 1 and ParaSDD2_Grear!='total'  " +
    			" and ParaSDD2_Grear is not null and  ParaSDD2_Grear <> '' " +
    			" order by convert(int,ParaSDD2_Grear) desc ";
    	listVclGear = baseDAO.execQuery(sql, new Object[]{});
    	return listVclGear;
    }
    
	/**
	 * 获取设备载重和EBP设定
	 * @param statTime
	 * @return
	 */
    public Map<String, Object> getVclLoadSet(String vclid){
    	Map<String, Object> listVclGear = new HashMap<String, Object>();
    	String sql = " select * from Vcl_LoadInfo where VclLI_VclId = "+vclid+" ";
    	listVclGear = baseDAO.execQueryOne(sql, new Object[]{});
    	return listVclGear;
    }
    
	/**
	 * 获取设备的海拔高度，按照设备ID,位置时间排序
	 * @return
	 */
	public List<Map<String, Object>> queryVclAtdeInfo(String statTime,String VehicleID) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(" select MsgT_Altitude as VclP_Altitude from ");
		strBuf.append(" "+GlobalDBVar.getDBFullName_Conn("Msg_Timing_"+statTime.replaceAll("-",""), "4", statTime)+" ");
		strBuf.append(" where MsgT_Vcl_ID="+VehicleID+" and MsgT_Altitude is not null and MsgT_Altitude <> '' order by MsgT_Vcl_ID,MsgT_MsgTime ");
		return this.baseDAO.execQuery(strBuf.toString(), new Object[]{});
	}

	/**
	 * 批量执行数据的插入
	 * 
	 **/
	public  boolean insertAllToTable(List<String> sqlList,List<Object[]> paramList) {
		boolean isOk = this.mcdstDAO.batch(sqlList, paramList);
		return isOk;

	}
	

}